package com.pengcheng.hngateway.service;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.pengcheng.hngateway.util.Common;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

@Service
@Slf4j
public class DbService {
    public String prefix="hnww";

    @Value("${dbtype}")
    private String dbtype;
    @Autowired
    public JdbcTemplate jdbcTemplate;

    @Autowired
    private Common common;

    private String split_string="'";

    private String[] number_type={"tinyint","smallint","mediumint","int","bigint","decimal","float","double","real","bit"};
    public List<Map<String,Object>> columns(String table)
    {
        String sql="";
        if(dbtype.equals("mysql"))
        {
            sql="show columns from "+prefix+"_"+table;
        }
        else
        {
            sql="SELECT column_name AS Field,CASE WHEN \"identity_start\" >0 THEN 'PRI' ELSE '' END AS fkey,\"udt_name\" AS ftype,table_name FROM  information_schema.COLUMNS WHERE  \"table_name\" = 'hnww_"+table+"';";
        }
        return jdbcTemplate.queryForList(sql);
    }
    public List<Map<String,Object>> query(List<Map<String,Object>> columns,String table,Map<String,String[]> condition,String order,String limit)
    {
        String where="";
        for(String key:condition.keySet())
        {
            String[] con=condition.get(key);
            if(con.length==2)
            {
                int index=-1;
                String column_name=key;
                for(int j=0;j<columns.size();j++)
                {
                    if(column_name.equals(columns.get(j).get("Field").toString()))
                    {
                        index=j;
                        j=columns.size();
                    }
                }
                if(index>=0)
                {
                    String type="";
                    if(dbtype.equals("mysql"))
                    {
                        type=columns.get(index).get("Type").toString();
                    }
                    else
                    {
                        type=columns.get(index).get("ftype").toString();//兼容麒麟
                    }
                    int index_number_type=-1;
                    for(int k=0;k<number_type.length;k++)
                    {
                        if(type.startsWith(number_type[k]))
                        {
                            index_number_type=k;
                            k=number_type.length;
                        }
                    }
                    String w="`"+column_name+"` "+con[0]+" ";
                    if(index_number_type>=0)
                    {
                        w+=(con[1]);
                    }
                    else {
                        w+=(split_string+con[1]+split_string);
                    }
                    if(!where.equals(""))
                    {
                        where+=(" and "+w);
                    }
                    else {
                        where+=w;
                    }
                }
            }
        }
        if(condition.get("_string")!=null&&condition.get("_string").length==1)
        {
            String w=condition.get("_string")[0];
            if(!where.equals(""))
            {
                where+=(" and "+w);
            }
            else {
                where+=w;
            }
        }
        String sql="select * from "+prefix+"_"+table;
        if(!where.equals(""))
        {
            sql+=(" where "+where);
        }
        if(!common.is_empty(order))
        {
            sql+=(" order by "+order);
        }
        if(!common.is_empty(limit))
        {
            sql+=(" limit "+limit);
        }
        try {
            log.info(sql);
            return jdbcTemplate.queryForList(sql);
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            return null;
        }
    }

    public int count(List<Map<String,Object>> columns,String table,Map<String,String[]> condition)
    {
        String where="";
        for(String key:condition.keySet())
        {
            String[] con=condition.get(key);
            if(con.length==2)
            {
                int index=-1;
                String column_name=key;
                for(int j=0;j<columns.size();j++)
                {
                    if(column_name.equals(columns.get(j).get("Field").toString()))
                    {
                        index=j;
                        j=columns.size();
                    }
                }
                if(index>=0)
                {
                    String type="";
                    if(dbtype.equals("mysql"))
                    {
                        type=columns.get(index).get("Type").toString();
                    }
                    else
                    {
                        type=columns.get(index).get("ftype").toString();//兼容麒麟
                    }
                    int index_number_type=-1;
                    for(int k=0;k<number_type.length;k++)
                    {
                        if(type.startsWith(number_type[k]))
                        {
                            index_number_type=k;
                            k=number_type.length;
                        }
                    }
                    String w="";
                    if(dbtype.equals("mysql"))
                    {
                        w="`"+column_name+"` "+con[0]+" ";
                    }
                    else
                    {
                        w="\""+column_name+"\" "+con[0]+" ";
                    }
                    if(index_number_type>=0)
                    {
                        w+=(con[1]);
                    }
                    else {
                        w+=(split_string+con[1]+split_string);
                    }
                    if(!where.equals(""))
                    {
                        where+=(" and "+w);
                    }
                    else {
                        where+=w;
                    }
                }
            }
        }
        if(condition.get("_string")!=null&&condition.get("_string").length==1)
        {
            String w=condition.get("_string")[0];
            if(!where.equals(""))
            {
                where+=(" and "+w);
            }
            else {
                where+=w;
            }
        }
        String sql="select count(*) as fcount from "+prefix+"_"+table;
        if(!where.equals(""))
        {
            sql+=(" where "+where);
        }
        try {
            log.info(sql);
            List<Map<String,Object>> rows= jdbcTemplate.queryForList(sql);
            if(rows!=null&&rows.size()>0)
            {
                return Integer.parseInt(rows.get(0).get("fcount").toString());
            }
            else {
                return -1;
            }
        }
        catch (Exception ex)
        {
            return -1;
        }
    }
    public BigDecimal sum(List<Map<String,Object>> columns, String table, Map<String,String[]> condition,String field)
    {
        String where="";
        for(String key:condition.keySet())
        {
            String[] con=condition.get(key);
            if(con.length==2)
            {
                int index=-1;
                String column_name=key;
                for(int j=0;j<columns.size();j++)
                {
                    if(column_name.equals(columns.get(j).get("Field").toString()))
                    {
                        index=j;
                        j=columns.size();
                    }
                }
                if(index>=0)
                {
                    String type="";
                    if(dbtype.equals("mysql"))
                    {
                        type=columns.get(index).get("Type").toString();
                    }
                    else
                    {
                        type=columns.get(index).get("ftype").toString();//兼容麒麟
                    }
                    int index_number_type=-1;
                    for(int k=0;k<number_type.length;k++)
                    {
                        if(type.startsWith(number_type[k]))
                        {
                            index_number_type=k;
                            k=number_type.length;
                        }
                    }
                    String w="`"+column_name+"` "+con[0]+" ";
                    if(index_number_type>=0)
                    {
                        w+=(con[1]);
                    }
                    else {
                        w+=(split_string+con[1]+split_string);
                    }
                    if(!where.equals(""))
                    {
                        where+=(" and "+w);
                    }
                    else {
                        where+=w;
                    }
                }
            }
        }
        if(condition.get("_string")!=null&&condition.get("_string").length==1)
        {
            String w=condition.get("_string")[0];
            if(!where.equals(""))
            {
                where+=(" and "+w);
            }
            else {
                where+=w;
            }
        }
        String sql="select sum("+field+") as fsum from "+prefix+"_"+table;
        if(!where.equals(""))
        {
            sql+=(" where "+where);
        }
        try {
            log.info(sql);
            List<Map<String,Object>> rows= jdbcTemplate.queryForList(sql);
            if(rows!=null&&rows.size()>0)
            {
                return new BigDecimal(rows.get(0).get("fsum").toString());
            }
            else {
                return new BigDecimal(-1);
            }
        }
        catch (Exception ex)
        {
            return new BigDecimal(-1);
        }
    }

    public int save(List<Map<String,Object>> columns,String table,Map<String,Object> row) throws Exception
    {
        String primary_field="";
        for(int i=0;i<columns.size();i++)
        {
            if(dbtype.equals("mysql"))
            {
                if(columns.get(i).get("Key").toString().toLowerCase().equals("pri"))
                {
                    primary_field=columns.get(i).get("Field").toString();
                }
            }
            else
            {
                if(columns.get(i).get("fkey").toString().toLowerCase().equals("pri"))
                {
                    primary_field=columns.get(i).get("Field").toString();
                }
            }
        }
        String sql="";
        List<Object> ps=new ArrayList<>();
        if(row.get(primary_field)!=null&&!(table.startsWith("dlog")&&table.length()>4))
        {
            sql="update "+prefix+"_"+table+" set ";
            String set="";
            for(String key:row.keySet())
            {
                //查询key是否存在
                int index=-1;
                for(int i=0;i<columns.size();i++)
                {
                    if(columns.get(i).get("Field").toString().equals(key))
                    {
                        index=i;
                    }
                }
                if(!key.equals(primary_field)&&index>=0)
                {
                    if(!set.equals(""))
                    {
                        set+=",";
                    }
                    if(dbtype.equals("mysql"))
                    {
                        set+=("`"+key+"`"+"=?");
                    }
                    else
                    {
                        set+=("\""+key+"\""+"=?");
                    }
//                    set+=(key+"=?");
                    ps.add(row.get(key));
                }
            }
            sql+=set;
            sql+=(" where "+primary_field+"=?");
            ps.add(row.get(primary_field));
            return jdbcTemplate.update(sql,ps.toArray());
        }
        else {
            sql="insert into "+prefix+"_"+table;
            String set="";
            String values="";
            for(String key:row.keySet())
            {
                int index=-1;
                for(int i=0;i<columns.size();i++)
                {
                    if(columns.get(i).get("Field").toString().equals(key))
                    {
                        index=i;
                    }
                }
                if(index>=0)
                {
                    if(!set.equals(""))
                    {
                        set+=",";
                    }
                    if(!values.equals(""))
                    {
                        values+=",";
                    }
                    if(dbtype.equals("mysql"))
                    {
                        set+=("`"+key+"`");
                    }
                    else
                    {
                        set+=("\""+key+"\"");
                    }
                    values+=("?");
                    ps.add(row.get(key));
                }
            }
            sql+=(" ("+set+") values ("+values+")");
            KeyHolder keyHolder = new GeneratedKeyHolder();
            final String new_sql=sql;
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement pstatement  = connection.prepareStatement(new_sql, Statement.RETURN_GENERATED_KEYS);
                    Object[] params=ps.toArray();
                    for(int i=0;i<params.length;i++)
                    {
                        pstatement.setObject((i+1), params[i]);
                    }
                    return pstatement;
                }
            }, keyHolder);
            if(keyHolder.getKeys().size()>1)
            {
                return (int)keyHolder.getKeys().get("id");
            }
            else
            {
                return keyHolder.getKey().intValue();
            }
        }
    }

    public List<Map<String,Object>> get_device_detail(List<Map<String,Object>> rows)
    {
        //查询最近一条dlog
        List<String> list_ids=new ArrayList<>();
        List<String> ids_detail=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            if(!list_ids.contains(rows.get(i).get("id")+""))
            {
                list_ids.add(rows.get(i).get("id")+"");
            }
            if(!ids_detail.contains(rows.get(i).get("detail_id")+""))
            {
                ids_detail.add(rows.get(i).get("detail_id")+"");
            }
        }
        String sql="select * from hnww_dloglast where device_id in ("+common.join(list_ids.toArray(new String[list_ids.size()]))+") order by ddate desc,id desc";
        List<Map<String,Object>> rows_dlog=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_dlog,"id","device_id","row_dlog");
        //查询对应的site
        sql="select * from hnww_site where id in ("+common.join(ids_detail.toArray(new String[ids_detail.size()]))+")";
        List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql);
        List<String> ids_site=new ArrayList<>();
        for(int i=0;i<rows_site.size();i++)
        {
            if(!ids_site.contains(rows_site.get(i).get("id")+""))
            {
                ids_site.add(rows_site.get(i).get("id")+"");
            }
        }
        if(rows_site.size()>0)
        {
            //获取当年定额
            int year=Integer.parseInt(common.format_date(new Date()).substring(0,4));
            sql="select * from hnww_limit where deleted=0 and site_id in ("+common.join(ids_site.toArray(new String[ids_site.size()]))+") and `year`=?";
            List<Map<String,Object>> rows_limit=jdbcTemplate.queryForList(sql,new Object[]{year});
            rows_site=fill_row(rows_site,rows_limit,"id","site_id","row_limit");
            //获取当年累计流量
            sql="select * from hnww_site_yearflow where dyear=? and detail_id in ("+common.join(ids_site.toArray(new String[ids_site.size()]))+")";
            List<Map<String,Object>> rows_yearflow=jdbcTemplate.queryForList(sql,new Object[]{year});
            rows_site=fill_row(rows_site,rows_yearflow,"id","detail_id","row_site_yearflow");
        }
        for(int i=0;i<rows.size();i++)
        {
            int dtype=Integer.parseInt(rows.get(i).get("dtype")+"");
            int detail_id=Integer.parseInt(rows.get(i).get("detail_id")+"");
            int index=-1;
            for(int j=0;j<rows_site.size();j++)
            {
                int id=Integer.parseInt(rows_site.get(j).get("id")+"");
                Map<String,Object> row_limit=(Map<String,Object>)rows_site.get(j).get("row_limit");
                Map<String,Object> row_site_yearflow=(Map<String,Object>)rows_site.get(j).get("row_site_yearflow");
                if(row_limit!=null&&row_site_yearflow!=null)
                {
                    double limit=Double.parseDouble(row_limit.get("flimit")+"");
                    double sum_flow=Double.parseDouble(row_site_yearflow.get("sum_flow")+"");
                    double percent=new BigDecimal(sum_flow*100/limit).setScale(2, RoundingMode.HALF_UP).doubleValue();
                    rows_site.get(j).put("percent",percent);
                }
                if(dtype==0&&detail_id==id)
                {
                    index=j;
                    j=rows_site.size();
                }
            }
            if(index>=0)
            {
                rows.get(i).put("row_site",rows_site.get(index));
            }
        }
        rows=fill_row(rows,rows_dlog,"id","device_id","row_dlog");

//        String sql="";
//        for(int i=0;i<rows.size();i++)
//        {
//            int device_id=Integer.parseInt(rows.get(i).get("id")+"");
//            sql="select * from "+prefix+"_dlog"+device_id+" where deleted=0 order by ddate desc limit 0,1";
//            List<Map<String,Object>> list_dlog1=jdbcTemplate.queryForList(sql);
//            if(list_dlog1.size()>0)
//            {
//                rows.get(i).put("row_dlog",list_dlog1.get(0));
//            }
//        }
        return rows;
    }
    public List<Map<String,Object>> get_dsendtask_detail(List<Map<String,Object>> rows)
    {
        //查询最近一条dlog
        String sql="";
        List<String> ids_dlog=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            int device_id=Integer.parseInt(rows.get(i).get("device_id")+"");
            int dlog_id=Integer.parseInt(rows.get(i).get("dlog_id")+"");
            if(device_id>0)
            {
                sql="select * from "+prefix+"_dlog"+device_id+" where deleted=0 and id=?";
                List<Map<String,Object>> list_dlog1=jdbcTemplate.queryForList(sql,new Object[]{dlog_id});
                if(list_dlog1.size()>0)
                {
                    list_dlog1=get_dlog_detail(list_dlog1);
                    rows.get(i).put("row_dlog",list_dlog1.get(0));
                }
            }
            else
            {
                sql="select * from "+prefix+"_dlog where deleted=0 and id=? and device_id>0";
                List<Map<String,Object>> list_dlog1=jdbcTemplate.queryForList(sql,new Object[]{dlog_id});
                if(list_dlog1.size()>0) {
                    list_dlog1 = get_dlog_detail(list_dlog1);
                    rows.get(i).put("row_dlog", list_dlog1.get(0));
                }
                else {
                    //循环查询了
                    if(!ids_dlog.contains(dlog_id+""))
                    {
                        ids_dlog.add(dlog_id+"");
                    }
                }
            }
        }
        if(ids_dlog.size()>0)
        {
            sql="select * from "+prefix+"_device where deleted=0";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql);
            for(int i=0;i<list_device.size();i++)
            {
                int device_id=Integer.parseInt(list_device.get(i).get("id")+"");
                sql="select * from "+prefix+"_dlog"+device_id+" where id in ("+common.join(ids_dlog.toArray(new String[ids_dlog.size()]))+")";
                List<Map<String,Object>> list_dlog=jdbcTemplate.queryForList(sql);
                if(list_dlog.size()>0)
                {
                    list_dlog = get_dlog_detail(list_dlog);
                }
                rows=fill_row(rows,list_dlog,"dlog_id","id","row_dlog");
            }
        }
        return rows;
    }
    public List<Map<String,Object>> get_dlog_detail(List<Map<String,Object>> rows)
    {
        //查询最近一条dlog
        List<String> list_ids=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            if(!list_ids.contains(rows.get(i).get("device_id")+""))
            {
                list_ids.add(rows.get(i).get("device_id")+"");
            }
        }
        String sql="select * from hnww_device where id in ("+common.join(list_ids.toArray(new String[list_ids.size()]))+")";
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"device_id","id","row_device");
        return rows;
    }
    public List<Map<String,Object>> get_device_wq_detail(List<Map<String,Object>> rows)
    {
        //查询最近一条dlog
        String sql="select * from hnww_dlog_wq where last=1 and device_id in ";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_dlog=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_dlog,"id","device_id","row_dlog_wq");
        return rows;
    }
    public List<Map<String,Object>> get_device_level_detail(List<Map<String,Object>> rows)
    {
        //查询最近一条dlog
        String sql="select * from hnww_dlog_level where last=1 and device_id in ";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_dlog=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_dlog,"id","device_id","row_dlog_level");
        return rows;
    }
    public List<Map<String,Object>> get_device_rain_detail(List<Map<String,Object>> rows)
    {
        //查询最近一条dlog
        String sql="select * from hnww_dlog_rain where last=1 and device_id in ";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_dlog=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_dlog,"id","device_id","row_dlog_rain");
        return rows;
    }
    public List<Map<String,Object>> get_device_video_detail(List<Map<String,Object>> rows)
    {
        //查询最近一条dlog
        String sql="select * from hnww_dlog_video where last=1 and device_id in ";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_dlog=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_dlog,"id","device_id","row_dlog_video");
        return rows;
    }
    public List<Map<String,Object>> get_resource_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String sql="select * from "+prefix+"_device"+" where deleted=0 and dtype=1 and detail_id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows_device=get_device_detail(rows_device);
        rows=fill_rows(rows,rows_device,"id","detail_id","rows_device");
        //row_division
        sql="select * from hnww_division where deleted=0";
        List<Map<String,Object>> rows_division=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_division,"division_id","id","row_division");
        //更新设备掉线数
        for(int i=0;i<rows.size();i++)
        {
            List<Map<String,Object>> list_device=(List<Map<String,Object>>) rows.get(i).get("rows_device");
            int fcount_offline=0;
            int fcount_device=0;
            if(list_device!=null)
            {
                fcount_device=list_device.size();
                for(int j=0;j<list_device.size();j++)
                {
                    String state=list_device.get(j).get("state").toString();
                    if(state.equals("0"))
                    {
                        fcount_offline++;
                    }
                }
            }
            rows.get(i).put("fcount_offline",fcount_offline);
            rows.get(i).put("fcount_device",fcount_device);
        }
        return rows;
    }
    public List<Map<String,Object>> get_permit_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String sql="select * from "+prefix+"_site"+" where deleted=0 and id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("site_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql);
        rows_site=get_site_detail(rows_site);
        rows=fill_row(rows,rows_site,"site_id","id","row_site");
        return rows;
    }
    public List<Map<String,Object>> get_advice_detail(List<Map<String,Object>> rows)
    {
        //查询row_resource
        String sql="select * from "+prefix+"_resource"+" where deleted=0 and id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("resource_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"resource_id","id","row_resource");
        //查询row_resource
        sql="select * from "+prefix+"_user"+" where deleted=0 and id in";
        ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("user_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_user=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_user,"user_id","id","row_user");
        //imgs,files
        for(int i=0;i<rows.size();i++)
        {
            String imgs=rows.get(i).get("imgs").toString();
            String files=rows.get(i).get("files").toString();
            String[] list_img=imgs.split(",");
            String[] list_file=files.split(",");
            rows.get(i).put("list_img",list_img);
            rows.get(i).put("list_file",list_file);
        }
        return rows;
    }
    public List<Map<String,Object>> get_adviceprocess_detail(List<Map<String,Object>> rows)
    {
        //查询row_advice
        String sql="select * from "+prefix+"_advice"+" where deleted=0 and id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("advice_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_advice=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_advice,"advice_id","id","row_advice");
        //查询row_resource
        sql="select * from "+prefix+"_user"+" where deleted=0 and id in";
        ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("user_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_user=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_user,"user_id","id","row_user");
        //imgs,files
        for(int i=0;i<rows.size();i++)
        {
            String imgs=rows.get(i).get("imgs").toString();
            String files=rows.get(i).get("files").toString();
            String[] list_img=imgs.split(",");
            String[] list_file=files.split(",");
            rows.get(i).put("list_img",list_img);
            rows.get(i).put("list_file",list_file);
        }
        return rows;
    }
    public List<Map<String,Object>> get_task_detail(List<Map<String,Object>> rows)
    {
        //查询row_resource
        String sql="select * from "+prefix+"_user"+" where deleted=0 and id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("user_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_user=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_user,"user_id","id","row_user");
        //查询row_resource
        sql="select * from "+prefix+"_user"+" where deleted=0 and id in";
        ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("to_user_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_to_user=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_to_user,"to_user_id","id","row_to_user");
        //imgs,files
        for(int i=0;i<rows.size();i++)
        {
            String imgs=rows.get(i).get("imgs").toString();
            String files=rows.get(i).get("files").toString();
            String[] list_img=imgs.split(",");
            String[] list_file=files.split(",");
            rows.get(i).put("list_img",list_img);
            rows.get(i).put("list_file",list_file);
        }
        return rows;
    }
    public List<Map<String,Object>> get_taskprocess_detail(List<Map<String,Object>> rows)
    {
        //查询row_advice
        String sql="select * from "+prefix+"_task"+" where deleted=0 and id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("task_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_task=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_task,"task_id","id","row_task");
        //查询row_resource
        sql="select * from "+prefix+"_user"+" where deleted=0 and id in";
        ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("user_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_user=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_user,"user_id","id","row_user");
        //imgs,files
        for(int i=0;i<rows.size();i++)
        {
            String imgs=rows.get(i).get("imgs").toString();
            String files=rows.get(i).get("files").toString();
            String[] list_img=imgs.split(",");
            String[] list_file=files.split(",");
            rows.get(i).put("list_img",list_img);
            rows.get(i).put("list_file",list_file);
        }
        return rows;
    }
    public List<Map<String,Object>> get_repair_detail(List<Map<String,Object>> rows)
    {
        //查询row_user
        String sql="select * from "+prefix+"_user"+" where deleted=0 and id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("user_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_user=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_user,"user_id","id","row_user");
        //查询row_resource
        sql="select * from "+prefix+"_device"+" where deleted=0 and id in";
        ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("device_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"device_id","id","row_device");
        //imgs,files
        for(int i=0;i<rows.size();i++)
        {
            String imgs=rows.get(i).get("imgs").toString();
            String files=rows.get(i).get("files").toString();
            String[] list_img=imgs.split(",");
            String[] list_file=files.split(",");
            rows.get(i).put("list_img",list_img);
            rows.get(i).put("list_file",list_file);
        }
        return rows;
    }
    public List<Map<String,Object>> get_video_detail(List<Map<String,Object>> rows)
    {
        //查询row_user
        String sql="select * from "+prefix+"_user"+" where deleted=0 and id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("user_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_user=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_user,"user_id","id","row_user");
        //查询row_resource
        sql="select * from "+prefix+"_river"+" where deleted=0 and id in";
        ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("river_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"river_id","id","row_river");
        //imgs,files
        return rows;
    }

    public List<Map<String,Object>> get_site_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String sql="select * from "+prefix+"_device"+" where deleted=0 and dtype=0 and detail_id in";
        String ids="";
        List<String> list_device_id=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_rows(rows,rows_device,"id","detail_id","rows_device");
        sql="select * from hnww_division where deleted=0";
        List<Map<String,Object>> rows_division=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_division,"division_id","id","row_division");
        //查询计费阶梯
        sql="select * from "+prefix+"_feestep"+" where deleted=0 and id in";
        ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("feestep_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_feestep=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_feestep,"feestep_id","id","row_feestep");
        //更新设备掉线数
        for(int i=0;i<rows.size();i++)
        {
            String device_ids="";
            List<Map<String,Object>> list_device=(List<Map<String,Object>>) rows.get(i).get("rows_device");
            int fcount_offline=0;
            if(list_device!=null)
            {
                for(int j=0;j<list_device.size();j++)
                {
                    String id=list_device.get(j).get("id")+"";
                    String state=list_device.get(j).get("state").toString();
                    if(state.equals("0"))
                    {
                        fcount_offline++;
                    }
                    if(!list_device_id.contains(id))
                    {
                        list_device_id.add(id);
                    }
                    if(!device_ids.equals(""))
                    {
                        device_ids+=",";
                    }
                    device_ids+=id;
                }
            }
            rows.get(i).put("fcount_offline",fcount_offline);
            rows.get(i).put("device_ids",device_ids);
        }
        String[] ids_device=list_device_id.toArray(new String[list_device_id.size()]);
        //获取当年定额
        sql="select * from hnww_limit where deleted=0 and site_id in ("+ids+")";
        List<Map<String,Object>> rows_limit=jdbcTemplate.queryForList(sql);
        rows=fill_rows(rows,rows_limit,"id","site_id","rows_limit");
        //获取用水计划
        sql="select * from hnww_plan where deleted=0 and state=1 and site_id in ("+ids+")";
        List<Map<String,Object>> rows_plan=jdbcTemplate.queryForList(sql);
        rows=fill_rows(rows,rows_limit,"id","site_id","rows_plan");
        return rows;
    }

    public List<Map<String,Object>> get_alarm_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String sql="select * from "+prefix+"_device"+" where deleted=0 and id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("device_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
//        rows_device=get_device_detail(rows_device);
        rows=fill_row(rows,rows_device,"device_id","id","row_device");
        //更新fdate,edate
        for(int i=0;i<rows.size();i++)
        {
            long ftime=Long.parseLong(rows.get(i).get("ftime").toString());
            long etime=Long.parseLong(rows.get(i).get("etime").toString());
            if(ftime>0)
            {
                rows.get(i).put("fdate",common.format_date(new Date(ftime)));
            }
            if(ftime>0)
            {
                rows.get(i).put("edate",common.format_date(new Date(etime)));
            }
        }
        //关联的alarmconfig
        sql="select * from "+prefix+"_alarmconfig"+" where deleted=0 and id in";
        ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("alarmconfig_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_alarmconfig=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_alarmconfig,"alarmconfig_id","id","row_alarmconfig");
        return rows;
    }

    public List<Map<String,Object>> get_user_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String sql="select * from "+prefix+"_role"+" where id in";
        String ids="";
        for(int i=0;i<rows.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=rows.get(i).get("role_id").toString();
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"role_id","id","row_role");
        return rows;
    }
    public List<Map<String,Object>> get_limit_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String sql="select * from "+prefix+"_site"+" where id in";
        List<String> list_id=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            if(!list_id.contains(rows.get(i).get("site_id").toString()))
            {
                list_id.add(rows.get(i).get("site_id").toString());
            }
        }
        String ids="";
        for(int i=0;i<list_id.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=list_id.get(i);
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"site_id","id","row_site");
        return rows;
    }
    public List<Map<String,Object>> get_plan_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String sql="select * from "+prefix+"_site"+" where id in";
        List<String> list_id=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            if(!list_id.contains(rows.get(i).get("site_id").toString()))
            {
                list_id.add(rows.get(i).get("site_id").toString());
            }
        }
        String ids="";
        for(int i=0;i<list_id.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=list_id.get(i);
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"site_id","id","row_site");
        List<String> list_year=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            if(!list_year.contains(rows.get(i).get("year").toString()))
            {
                list_year.add(rows.get(i).get("year").toString());
            }
        }
        String years="";
        for(int i=0;i<list_year.size();i++)
        {
            if(!years.equals(""))
            {
                years+=",";
            }
            years+=list_year.get(i);
        }
        sql="select * from hnww_limit where site_id in ("+ids+") and deleted=0 and year in ("+years+")";
        List<Map<String,Object>> rows_limit=jdbcTemplate.queryForList(sql);
        for(int i=0;i<rows.size();i++)
        {
            Map<String,Object> row=rows.get(i);
            int index=-1;
            for(int j=0;j<rows_limit.size();j++)
            {
                Map<String,Object> row_limit=rows_limit.get(j);
                if(row.get("site_id").toString().equals(row_limit.get("site_id").toString())&&row.get("year").toString().equals(row_limit.get("year").toString()))
                {
                    index=j;
                    j=rows_limit.size();
                }
            }
            if(index>=0)
            {
                rows.get(i).put("row_limit",rows_limit.get(index));
            }
        }
        return rows;
    }
    public List<Map<String,Object>> get_fee_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String sql="select * from "+prefix+"_site"+" where id in";
        List<String> list_id=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            if(!list_id.contains(rows.get(i).get("site_id").toString()))
            {
                list_id.add(rows.get(i).get("site_id").toString());
            }
        }
        String ids="";
        for(int i=0;i<list_id.size();i++)
        {
            if(!ids.equals(""))
            {
                ids+=",";
            }
            ids+=list_id.get(i);
        }
        sql+=(" ("+ids+")");
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"site_id","id","row_site");
        String[] ids_id=new String[rows.size()];
        for(int i=0;i<rows.size();i++)
        {
            ids_id[i]=rows.get(i).get("id").toString();
        }
        sql="select * from hnww_feelist where fee_id in ("+common.join(ids_id)+")";
        List<Map<String,Object>> rows_feelist=jdbcTemplate.queryForList(sql);
        if(rows_feelist.size()>0)
        {
            rows_feelist=get_feelist_detail(rows_feelist);
        }
        rows=fill_rows(rows,rows_feelist,"id","fee_id","rows_feelist");
        return rows;
    }
    public List<Map<String,Object>> get_feelist_detail(List<Map<String,Object>> rows)
    {
        //查询row_device
        String[] ids_device=new String[rows.size()];
        for(int i=0;i<rows.size();i++)
        {
            ids_device[i]=rows.get(i).get("device_id").toString();
        }
        String sql="select * from hnww_device where id in ("+common.join(ids_device)+")";
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_device,"device_id","id","row_device");
        return rows;
    }
    public List<Map<String,Object>> get_field_detail(List<Map<String,Object>> rows)
    {
        for(int i=0;i<rows.size();i++)
        {
            Map<String,Object> row=rows.get(i);
            String dictionary=row.get("dictionary").toString();
            String[] list_dictionary=dictionary.split(",");
            rows.get(i).put("list_dictionary",list_dictionary);
        }
        return rows;
    }

    public List<Map<String,Object>> get_category_detail(List<Map<String,Object>> rows)
    {
        for(int i=0;i<rows.size();i++)
        {
            String path=rows.get(i).get("path").toString();
            String[] list_path=path.split(",");
            rows.get(i).put("level",list_path.length);
            String fullname=rows.get(i).get("name").toString();
            for(int j=0;j< list_path.length-1;j++)
            {
                fullname="-"+fullname;
            }
            rows.get(i).put("fullname",fullname);
        }
        return rows;
    }
    public List<Map<String,Object>> get_menu_detail(List<Map<String,Object>> rows)
    {
        String[] ids=new String[rows.size()];
        for(int i=0;i<rows.size();i++)
        {
            ids[i]=rows.get(i).get("id").toString();
        }
        String sql="select * from hnww_menu where deleted=0 and pid in ("+common.join(ids)+") order by fsort asc";
        List<Map<String,Object>> rows_cmenu=jdbcTemplate.queryForList(sql);
        rows=fill_rows(rows,rows_cmenu,"id","pid","rows_cmenu");
        return rows;
    }
    public List<Map<String,Object>> get_dayflow_detail(List<Map<String,Object>> rows)
    {
        List<String> list_device=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            String device_id=rows.get(i).get("device_id")+"";
            if(list_device.indexOf(device_id)<0)
            {
                list_device.add(device_id);
            }
        }
        if(list_device.size()>0)
        {
            String sql="select * from hnww_device where deleted=0 and id in ("+common.join_list(list_device)+")";
            List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_device,"device_id","id","row_device");
        }
        return rows;
    }
    public List<Map<String,Object>> get_monthflow_detail(List<Map<String,Object>> rows)
    {
        List<String> list_device=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            String device_id=rows.get(i).get("device_id")+"";
            if(list_device.indexOf(device_id)<0)
            {
                list_device.add(device_id);
            }
        }
        if(list_device.size()>0)
        {
            String sql="select * from hnww_device where deleted=0 and id in ("+common.join_list(list_device)+")";
            List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_device,"device_id","id","row_device");
        }
        return rows;
    }
    public List<Map<String,Object>> get_quarterflow_detail(List<Map<String,Object>> rows)
    {
        List<String> list_device=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            String device_id=rows.get(i).get("device_id")+"";
            if(list_device.indexOf(device_id)<0)
            {
                list_device.add(device_id);
            }
        }
        if(list_device.size()>0)
        {
            String sql="select * from hnww_device where deleted=0 and id in ("+common.join_list(list_device)+")";
            List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_device,"device_id","id","row_device");
        }
        return rows;
    }
    public List<Map<String,Object>> get_yearflow_detail(List<Map<String,Object>> rows)
    {
        List<String> list_device=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            String device_id=rows.get(i).get("device_id")+"";
            if(list_device.indexOf(device_id)<0)
            {
                list_device.add(device_id);
            }
        }
        if(list_device.size()>0)
        {
            String sql="select * from hnww_device where deleted=0 and id in ("+common.join_list(list_device)+")";
            List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_device,"device_id","id","row_device");
        }
        return rows;
    }
    public List<Map<String,Object>> get_site_dayflow_detail(List<Map<String,Object>> rows)
    {
        List<String> list_site=new ArrayList<>();
        List<String> list_resource=new ArrayList<>();
        for(int i=0;i<rows.size();i++)
        {
            String detail_id=rows.get(i).get("detail_id")+"";
            String dtype=rows.get(i).get("dtype")+"";
            if(dtype.equals("0"))
            {
                if(list_site.indexOf(detail_id)<0)
                {
                    list_site.add(detail_id);
                }
            }
            else if(dtype.equals("1"))
            {
                if(list_resource.indexOf(detail_id)<0)
                {
                    list_resource.add(detail_id);
                }
            }
        }
        if(list_site.size()>0)
        {
            String sql="select * from hnww_site where deleted=0 and id in ("+common.join_list(list_site)+")";
            List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_site,"detail_id","id","row_site");
        }
        if(list_resource.size()>0)
        {
            String sql="select * from hnww_resource where deleted=0 and id in ("+common.join_list(list_resource)+")";
            List<Map<String,Object>> rows_resource=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_resource,"detail_id","id","row_resource");
        }
        return rows;
    }
    public List<Map<String,Object>> get_site_monthflow_detail(List<Map<String,Object>> rows)
    {
        List<String> list_site=new ArrayList<>();
        List<String> list_resource=new ArrayList<>();
        String sql="";
        for(int i=0;i<rows.size();i++)
        {
            String detail_id=rows.get(i).get("detail_id")+"";
            String dtype=rows.get(i).get("dtype")+"";
            if(dtype.equals("0"))
            {
                if(list_site.indexOf(detail_id)<0)
                {
                    list_site.add(detail_id);
                }
            }
            else if(dtype.equals("1"))
            {
                if(list_resource.indexOf(detail_id)<0)
                {
                    list_resource.add(detail_id);
                }
            }
            int dyear=Integer.parseInt(rows.get(i).get("dyear")+"");
            int dmonth=Integer.parseInt(rows.get(i).get("dmonth")+"");
            sql="select * from hnww_site_dayflow where dtype=? and dmonth=? and detail_id=? and dyear=? order by sum_flow desc limit 0,1";
            List<Map<String,Object>> rows_max_daflow=jdbcTemplate.queryForList(sql,new Object[]{dtype,dmonth,detail_id,dyear});
            if(rows_max_daflow.size()>0)
            {
                rows.get(i).put("max_dayflow",rows_max_daflow.get(0));
            }
            sql="select * from hnww_site_dayflow where dtype=? and dmonth=? and detail_id=? and dyear=? order by sum_flow asc limit 0,1";
            List<Map<String,Object>> rows_min_daflow=jdbcTemplate.queryForList(sql,new Object[]{dtype,dmonth,detail_id,dyear});
            if(rows_min_daflow.size()>0)
            {
                rows.get(i).put("min_dayflow",rows_min_daflow.get(0));
            }
        }
        if(list_site.size()>0)
        {
            sql="select * from hnww_site where deleted=0 and id in ("+common.join_list(list_site)+")";
            List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_site,"detail_id","id","row_site");
        }
        if(list_resource.size()>0)
        {
            sql="select * from hnww_resource where deleted=0 and id in ("+common.join_list(list_resource)+")";
            List<Map<String,Object>> rows_resource=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_resource,"detail_id","id","row_resource");
        }
        return rows;
    }
    public List<Map<String,Object>> get_site_quarterflow_detail(List<Map<String,Object>> rows)
    {
        List<String> list_site=new ArrayList<>();
        String sql="";
        for(int i=0;i<rows.size();i++)
        {
            String detail_id=rows.get(i).get("detail_id")+"";
            if(list_site.indexOf(detail_id)<0)
            {
                list_site.add(detail_id);
            }
            int dyear=Integer.parseInt(rows.get(i).get("dyear")+"");
            int dquarter=Integer.parseInt(rows.get(i).get("dquarter")+"");
            int smonth=(dquarter-1)*3+1;
            int emonth=(dquarter)*3;
            sql="select * from hnww_site_monthflow where dtype=0 and dmonth>=? and dmonth<=? and detail_id=? and dyear=? order by sum_flow desc limit 0,1";
            List<Map<String,Object>> rows_max_monthflow=jdbcTemplate.queryForList(sql,new Object[]{smonth,emonth,detail_id,dyear});
            if(rows_max_monthflow.size()>0)
            {
                rows.get(i).put("max_monthflow",rows_max_monthflow.get(0));
            }
            sql="select * from hnww_site_monthflow where dtype=0 and dmonth>=? and dmonth<=? and detail_id=? and dyear=? order by sum_flow asc limit 0,1";
            List<Map<String,Object>> rows_min_monthflow=jdbcTemplate.queryForList(sql,new Object[]{smonth,emonth,detail_id,dyear});
            if(rows_min_monthflow.size()>0)
            {
                rows.get(i).put("min_monthflow",rows_min_monthflow.get(0));
            }
        }
        if(list_site.size()>0)
        {
            sql="select * from hnww_site where deleted=0 and id in ("+common.join_list(list_site)+")";
            List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_site,"detail_id","id","row_site");
        }
        return rows;
    }
    public List<Map<String,Object>> get_site_yearflow_detail(List<Map<String,Object>> rows)
    {
        List<String> list_site=new ArrayList<>();
        String sql="";
        for(int i=0;i<rows.size();i++)
        {
            String detail_id=rows.get(i).get("detail_id")+"";
            if(list_site.indexOf(detail_id)<0)
            {
                list_site.add(detail_id);
            }
            int dyear=Integer.parseInt(rows.get(i).get("dyear")+"");
            sql="select * from hnww_site_monthflow where dtype=0 and detail_id=? and dyear=? order by sum_flow desc limit 0,1";
            List<Map<String,Object>> rows_max_monthflow=jdbcTemplate.queryForList(sql,new Object[]{detail_id,dyear});
            if(rows_max_monthflow.size()>0)
            {
                rows.get(i).put("max_monthflow",rows_max_monthflow.get(0));
            }
            sql="select * from hnww_site_monthflow where dtype=0 and detail_id=? and dyear=? order by sum_flow asc limit 0,1";
            List<Map<String,Object>> rows_min_monthflow=jdbcTemplate.queryForList(sql,new Object[]{detail_id,dyear});
            if(rows_min_monthflow.size()>0)
            {
                rows.get(i).put("min_monthflow",rows_min_monthflow.get(0));
            }
        }
        if(list_site.size()>0)
        {
            sql="select * from hnww_site where deleted=0 and id in ("+common.join_list(list_site)+")";
            List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql);
            rows=fill_row(rows,rows_site,"detail_id","id","row_site");
        }
        return rows;
    }
    public List<Map<String,Object>> get_roledetail_detail(List<Map<String,Object>> rows)
    {
        for(int i=0;i<rows.size();i++)
        {
            String power=rows.get(i).get("power")==null?"":rows.get(i).get("power").toString();
            String[] list_power=power.split(",");
            for(int j=0;j<list_power.length;j++)
            {
                rows.get(i).put("p"+list_power[j],"1");
            }
        }
        return rows;
    }
    public List<Map<String,Object>> get_feeitem_detail(List<Map<String,Object>> rows)
    {
        String[] ids=new String[rows.size()];
        for(int i=0;i<rows.size();i++)
        {
            ids[i]=rows.get(i).get("industry_id").toString();
        }
        String sql="select * from hnww_industry where id in ("+common.join(ids)+")";
        List<Map<String,Object>> rows_industry=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_industry,"industry_id","id","row_industry");
        return rows;
    }

    public List<Map<String,Object>> get_article_detail(List<Map<String,Object>> rows)
    {
        String[] ids=new String[rows.size()];
        String[] ids_river=new String[rows.size()];
        for(int i=0;i<rows.size();i++)
        {
            ids[i]=rows.get(i).get("category_id").toString();
            ids_river[i]=rows.get(i).get("river_id").toString();
        }
        String sql="select * from hnww_category where id in ("+common.join(ids)+")";
        List<Map<String,Object>> rows_category=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_category,"category_id","id","row_category");
        sql="select * from hnww_river where id in ("+common.join(ids_river)+")";
        List<Map<String,Object>> rows_river=jdbcTemplate.queryForList(sql);
        rows=fill_row(rows,rows_river,"river_id","id","row_river");
        List<Map<String,Object>> columns=columns("article");
        for(int i=0;i<columns.size();i++)
        {
            String column=columns.get(i).get("Field").toString();
            if(column.startsWith("field_"))
            {
                for(int j=0;j<rows.size();j++)
                {
                    String val=rows.get(j).get(column).toString();
                    String[] list_val=val.split(",");
                    rows.get(j).put("list_"+column,list_val);
                }
            }
        }
        return rows;
    }

    public void ini_category_path(int type)
    {
        String sql="select * from hnww_category where deleted=0 and type="+type;
        List<Map<String,Object>> rows_category=jdbcTemplate.queryForList(sql);
        for(int i=0;i<rows_category.size();i++)
        {
            Map<String,Object> row_category=rows_category.get(i);
            String path="";
            String pid=row_category.get("pid").toString();
            while (!pid.equals("0"))
            {
                int index=-1;
                for(int j=0;j<rows_category.size();j++)
                {
                    if(rows_category.get(j).get("id").toString().equals(pid))
                    {
                        index=j;
                        j=rows_category.size();
                    }
                }
                if(index>=0)
                {
                    path=(","+pid+path);
                    pid=rows_category.get(index).get("pid").toString();
                }
                else {
                    pid="0";
                    path="-";
                }
            }
            if(path.equals("-"))
            {
                sql="update hnww_category set deleted=1 where id="+row_category.get("id").toString();
                jdbcTemplate.update(sql);
            }
            else {
                path="0"+path;
                if(!path.equals(row_category.get("path")))
                {
                    sql="update hnww_category set path='"+path+"' where id="+row_category.get("id").toString();
                    jdbcTemplate.update(sql);
                }
            }
        }
    }

    public List<Map<String,Object>> fill_row(List<Map<String,Object>> rows1,List<Map<String,Object>> rows2,String key1,String key2,String column_name)
    {
        for(int i=0;i<rows1.size();i++)
        {
            for(int j=0;j<rows2.size();j++)
            {
                if(rows1.get(i).get(key1).toString().equals(rows2.get(j).get(key2).toString()))
                {
                    rows1.get(i).put(column_name,rows2.get(j));
                    j=rows2.size();
                }
            }
        }
        return rows1;
    }
    public List<Map<String,Object>> fill_rows(List<Map<String,Object>> rows1,List<Map<String,Object>> rows2,String key1,String key2,String column_name)
    {
        for(int i=0;i<rows1.size();i++)
        {
            List<Map<String,Object>> rows3=new ArrayList<>();
            for(int j=0;j<rows2.size();j++)
            {
                if(rows1.get(i).get(key1).toString().equals(rows2.get(j).get(key2).toString()))
                {
                    rows3.add(rows2.get(j));
                }
            }
            rows1.get(i).put(column_name,rows3);
        }
        return rows1;
    }
    public Map<String,Object> get_config()
    {
        String sql="select * from hnww_config where deleted=0";
        List<Map<String,Object>> rows_config=jdbcTemplate.queryForList(sql);
        Map<String,Object> config=new HashMap<>();
        for(int i=0;i<rows_config.size();i++)
        {
            Map<String,Object> row_config=rows_config.get(i);
            config.put(row_config.get("name")+"",row_config.get("val")+"");
        }
        return config;
    }
}
